import json
import time

import requests
import pymysql



class ScriptForXiaomi(object):

    def __init__(self):
        self.url = "https://www.dianxiaomi.com/package/getStateCount.json"
        self.headers = {"User-Agent": "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36",
                   "Cookie": "_ati=1800950527900; dxm_i=MjAxMjg5IWFUMHlNREV5T0RrIWRkMDYzYWZmOTMyNDhhZmZhMjA0N2FiNTJlY2FjZjg1; dxm_t=MTU1NDg4MzYxNSFkRDB4TlRVME9EZ3pOakUxIWZjNzliYTNlZWZhODAwMzcyYzVjNDU3MmY4MWEyZTE1; dxm_c=dEpGZTBubWkhWXoxMFNrWmxNRzV0YVEhOTU1MzljYjI0NTBmYjA0YWNmNTcwZmMyMTdkNmQ1MjQ; dxm_w=NTYxNmM0NjQ1OTBjOGFiOTVkNjk2YTQzMWZmNjA1ZWIhZHowMU5qRTJZelEyTkRVNU1HTTRZV0k1TldRMk9UWmhORE14Wm1ZMk1EVmxZZyE3NzljOTBlNzUwZTlmM2FjNjMzODViM2YwNjczZmZhNQ; dxm_s=y-_gYWPa5TFriBQ5TbPQNv1A3rLJzzFA3K26i57uccE; _dxm_ad_client_id=41CFEB6BFCFE954A87F1B34F17B64DB64; Hm_lvt_f8001a3f3d9bf5923f780580eb550c0b=1555131734,1555133542,1555205488,1555899179; Hm_lpvt_f8001a3f3d9bf5923f780580eb550c0b=1555899334; JSESSIONID=8D727E777E6F76B97AF3D48F3B4E8562; rememberMe=HAdNo7GyxNvfaZ9Y3b7BxKNEjWi1eZN5U0D11yqch0Pp4sGfyGYHJPYdFHYaJmHotq9J1plv+9ABcz67HKLbqzqoxXqOcmHM36gsEbYhQkmpU5Tlo43s1ECR9vLcZQ1evOMnH3FlENvuB/UfZya6GkaFOn4RTrqISoCwesN/D2ZzvcnVwFdp5KZipLrP2h85ifo0CE5MoA+TTo+cQ2HtF/OhqbwJItf5ZFmmEqQ/WJV/UMDnl/reoU4d3QhOPFsiAAXifb2ulQjLZP9jOpop2VS0NKu4dNsePXFRXGRqwiEdu+shKdE/h9UJSE5t2i2KZycYLWMqGJlLLFJQLaiCo51jey9PSevdbP1NLlka/zCmv2cXxxt08EbiKWZjT3N9IaHbH75EozQAs7CvpAx6JpJEYzrrBAjJbIdspzTO/BGIzI0D07cBJsgXwEDIt/A6hbwQlq2YczKQJce1v5nFny22hgZO32VG//bBne8b83ofZKyKO9qeB0uRiYOhunQFrYXqZ1spyATPK26VpHklx+VILOqRPQQsPOwuUc5uxGs="}



    def get_response_data(self):
        # 获取店小秘订单基本数据
        try:
            response = requests.get(url=self.url, headers=self.headers)
        except Exception as e:
            print(e)
            print(response.status_code)
            print(response.content.decode())
        return response.content.decode()

    def counter_data(self, json_dict):
        # 数据转换与提取
        str_dict = json.loads(json_dict)['orderCount']
        data_list = [str_dict['paidCount'],
                     str_dict['approvedCount'],
                     str_dict['processedCount'],
                     str_dict['processedOverseaCount'],
                     str_dict['allocatedHasCount'],
                     str_dict['allocatedOutCount'],
                     str_dict['commitSuccessNum'],
                     str_dict['commitFailNum'],
                     str_dict['refoundCount'],
                     str_dict['voidedCount']]
        data_of_tuple = tuple(data_list)

        return data_of_tuple

    def data_to_mysql(self, cur, data_of_list):
        "数据存入mysql"
        sql = """UPDATE tb_order_counts SET to_examine=%s,
        to_handle=%s,private_warehouse=%s,the_third_warehouse=%s,have_goods=%s,
        no_goods=%s,deliver_goods_failed=%s,deliver_goods_success=%s,
        refused=%s,shelve=%s WHERE id = 1""" % data_of_list
        cur.execute(sql)


# 5  34  0  0  9  8  0  991 0 38

if __name__ == "__main__":

    i = 1
    while True:   # 定时任务

        # 和数据库建立连接
        conn = pymysql.connect('localhost', 'vne123', '123', charset='utf8')

        # 创建游标
        cur = conn.cursor()

        # 选择数据库

        cur.execute('use vnemy')

        # 导入数据
        # 1 创建对象

        sfx = ScriptForXiaomi()

        # 2 爬取数据
        response = sfx.get_response_data()

        # 3 数据转换与提取
        data_of_tuple = sfx.counter_data(response)

        # 4 将数据存入mysql
        sfx.data_to_mysql(cur, data_of_tuple)

        cur.close()
        conn.commit()
        # 关闭数据库服务器连接，释放内存
        conn.close()


        time.sleep(1)
        i += 1
        print("更新第%s次" % i)




# 1、循环3216次   json解码错误
# 2、 运行第178次    requests.exceptions.ConnectionError:
# HTTPSConnectionPool(host='www.dianxiaomi.com', port=443):
# Max retries exceeded with url: /package/getStateCount.json (Caused by NewConnectionError('<urllib3.connection.
# VerifiedHTTPSConnection object at 0x0000000003525470>:
# Failed to establish a new connection: [WinError 10060] 由于连接方在一段时间后没有正确答复或连接的主机没有反应，连接尝试失败。'))
# 3、 第105次     原因同第二次